<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018-09-29
 * Time: 13:48
 */

namespace backend\controllers;


use app\models\CrmHwzx;
use app\models\CrmHwzxHistoryGt;
use app\models\CrmMallOrder;
use app\models\CrmNotify;
use app\models\CrmSystemYgxx;
use app\models\CrmSystemYgxx_;
use app\models\CtiCdr;
use app\models\search\CrmHwzxSearch;
use app\models\search\CrmSystemYgxxSearch;
use common\helps\Tools;
use FreeSwitchEventListener;
use yii\base\Object;
use yii\helpers\ArrayHelper;

class BasicInformationSystemTjController  extends CommonController
{

    public $todaySec = '';
    public $weekSec = '';
    public $monthSec = '';
    public function beforeAction($acUtion)
    {
        $this->todaySec = strtotime(date('Y-m-d'));
        $this->weekSec = strtotime(date('Y-m-d').' -1 week');
        $this->monthSec = strtotime(date('Y-m-d').' -1 year');
        return parent::beforeAction($acUtion);
    }

    public function actionTodayData(){
        $allData['todayCall'] = $this->todayCall();
        $allData['todayContact'] = $this->todayContact();
        $allData['todayOrdered'] = $this->todayOrdered();
        $allData['configSouye'] = $this->configSouye();
        $allData['notify'] = $this->notify();
        $allData['todayAddIntentAccount'] = $this->todayAddIntentAccount();
        $allData['targetFinishedPercent'] = $this->targetFinishedPercent();
        return json_encode($allData);
    }
    //今天目标完成率
    public function targetFinishedPercent(){
        $ygxx = CrmSystemYgxx_::find()->select('config_other')->where(['user_id'=>\Yii::$app->user->getId()])->one();
        $outBoundNumber = CtiCdr::find()->select('*')->where(['direction'=>'inbound','user_name'=>$_COOKIE['zuoxi']])->andWhere(['>','create_at',$this->todaySec])->count();
        $outBoundTime = CtiCdr::find()->select('*')->where(['direction'=>'inbound','user_name'=>$_COOKIE['zuoxi']])->andWhere(['>','create_at',$this->todaySec])->sum('duration');
        $signedMoney = CrmMallOrder::find()->select('*')->where(['gsid'=>\Yii::$app->user->getId()])->andWhere(['>','xdsj',$this->todaySec])->sum('zje');
        return [
            'outBoundNumber'=>[
                'outBoundNumber_'=>(int)$outBoundNumber,
                'outBoundNumber'=>$ygxx->config_other['target']['outBoundNumber']
            ],
             'outBoundTime'=>[
                'outBoundTime_'=>(int)$outBoundTime,
                'outBoundTime'=>$ygxx->config_other['target']['outBoundTime']
             ],
            'signedMoney'=>[
                'signedMoney_'=>(int)$signedMoney,
                'signedMoney'=>$ygxx->config_other['target']['signedMoney']
            ]
        ];
    }
    //今日呼叫情况
    public function todayCall(){
        //        总通话数
        $allCount = CtiCdr::find()->where(['user_name'=>$_POST['zuoxi']])->andWhere(['>','create_at',$this->todaySec])->count();
        //        通话总时长
        $allDuration = CtiCdr::find()->where(['user_name'=>$_POST['zuoxi']])->andWhere(['>','create_at',$this->todaySec])->sum('duration');
        //        呼入总数
        $inCount = CtiCdr::find()->where(['sip_to_user'=>$_POST['zuoxi']])->andWhere(['>','create_at',$this->todaySec])->count();
        //        呼出总数
        $outCount = CtiCdr::find()->where(['sip_from_user'=>$_POST['zuoxi']])->andWhere(['>','create_at',$this->todaySec])->count();
        //        呼出总时长
        $outDuration = CtiCdr::find()->where(['sip_from_user'=>$_POST['zuoxi']])->andWhere(['>','create_at',$this->todaySec])->sum('duration');
        //        呼入总时长
        $inDuration = CtiCdr::find()->where(['sip_to_user'=>$_POST['zuoxi']])->andWhere(['>','create_at',$this->todaySec])->sum('duration');
        $todayCall['color'] = 'rgb(63, 161, 255)';
        $todayCall['span'] = 4;
        $todayCall['data'] = '4';
        $data[0]['title'] = '总通话数';
        $data[0]['count'] = $allCount.'（个）';
        $data[0]['icon'] = '';
        $data[1]['title'] = '通话总时长';
        $data[1]['count'] = Tools::secToTime($allDuration);
        $data[1]['icon'] = '';
        $data[2]['title'] = '呼入总数';
        $data[2]['count'] = $inCount.'（个）';
        $data[2]['icon'] = '';
        $data[3]['title'] = '呼出总数';
        $data[3]['count'] = $outCount.'（个）';
        $data[3]['icon'] = '';
        $data[4]['title'] = '呼出总时长';
        $data[4]['count'] = Tools::secToTime($outDuration);
        $data[4]['icon'] = '';
        $data[5]['title'] = '呼入总时长';
        $data[5]['count'] = Tools::secToTime($inDuration);
        $data[5]['icon'] = '';
        $todayCall['data'] = $data;
        return $todayCall;
    }
    //今日呼叫情况
    public function todayContact(){
        //        今日待跟踪
        $dgz = CrmHwzx::find()->where(['gsid'=>\Yii::$app->user->getId()])->andWhere(['>','gxsj',$this->todaySec])->andWhere(['is not','khxq',null])->count();
        //        今日已联系
        $ylx = CrmHwzx::find()->where(['gsid'=>\Yii::$app->user->getId()])->andWhere(['>','gxsj',$this->todaySec])->count();
        //        新增意向
        $xzyx = CrmHwzx::find()->where(['gsid'=>\Yii::$app->user->getId()])->andWhere(['>','gxsj',$this->todaySec])->andWhere(['is','wcj',null])->count();
        $todayContact['color'] = 'rgb(63, 161, 255)';
        $todayContact['span'] = '4';
        $todayContact['data'] = '4';
        $data[0]['title'] = '今日待跟踪';
        $data[0]['count'] = $dgz.'（个）';
        $data[0]['icon'] = '';
        $data[1]['title'] = '今日已联系';
        $data[1]['count'] = $ylx.'（个）';
        $data[1]['icon'] = '';
        $data[2]['title'] = '新增意向';
        $data[2]['count'] = $xzyx.'（个）';
        $data[2]['icon'] = '';
        $todayContact['data'] = $data;
        return $todayContact;
    }

    //今日下订单情况
    public function todayOrdered(){
        $connection  = \Yii::$app->db;
        //        下订单次数
        $dgz = CrmMallOrder::find()->where(['gsid'=>\Yii::$app->user->getId()])->andWhere(['>','xdsj',$this->todaySec])->count();
        //        下订单金额
        $sql = "select sum(zje) from crm_mall_order where gsid=".\Yii::$app->user->getId() . " and xdsj >" . $this->todaySec;
        $ylx = $connection->createCommand($sql)->queryOne();
        if (count($ylx)>0){
            $ylx = $ylx['sum(zje)'];
            $len = (int)(strlen($ylx)/4)+1;
            for ($i=$len-1;$i>=0;$i--){
                $arr[$i] = substr($ylx,$i*4,4);
            }
            $str = '';
            foreach ($arr as $k=>$v){
                $str .= ','.$v;
            }
            $ylx = trim($str,',');
        }
        $todayOrdered['color'] = 'rgb(63, 161, 255)';
        $todayOrdered['span'] = '4';
        $todayOrdered['data'] = '4';
        $data[0]['title'] = '下订单次数';
        $data[0]['count'] = $dgz.'（个）';
        $data[0]['icon'] = '';
        $data[1]['title'] = '下订单金额';
        $data[1]['count'] = $ylx.'（元）';
        $data[1]['icon'] = '';
        $todayOrdered['data'] = $data;
        return $todayOrdered;
    }
    //首页的配置
    public function configSouye(){
        $ygxx = CrmSystemYgxx_::find()->select('config_souye')->where(['user_id'=>\Yii::$app->user->getId()])->one();
        return $ygxx->config_souye;
    }
    //通知公告
    public function notify(){
        $all = CrmNotify::find()->select('*')->limit('4')->orderBy('update_at desc')->asArray()->all();
        return $all;
    }
    //今天新增意图客户
    public function todayAddIntentAccount(){
        $search = new CrmHwzxSearch();
        $pro = $search->search(\Yii::$app->request->post());
        $pro->query->andWhere(['>','gxsj',$this->todaySec])->andWhere(['is not','khxq',null])->andWhere(['!=','khxq','']);
        $all = $pro->query->limit('4')->orderBy('gxsj desc')->asArray()->all();
        return $all;
    }

    //拿到统计配置的 列表
    public function actionList(){
        $ygxx = new CrmSystemYgxxSearch();
        $pro = $ygxx->search(\Yii::$app->request->post());
        $count = $pro->query->count();
        isset($_POST['limit'])?$pro->query->limit($_POST['limit']):"";
        isset($_POST['page'])?$pro->query->offset($_POST['limit']*($_POST['page']-1)):"";
        $res = ArrayHelper::toArray($pro->query->all());
        return $this->actionLayData($count,$res,1,"");
    }

    /**
     * 保存数据
     * @return string
     */
    public function actionSave(){
        $model = new CrmSystemYgxx_();
        $post = \Yii::$app->request->post();
        $ygxx = $model->find()->select('*')->where(['id'=>$post['CrmSystemYgxx_']['id']])->one();
        $post['CrmSystemYgxx_']['config_souye'] = json_decode($post['CrmSystemYgxx_']['config_souye']);
        $post['CrmSystemYgxx_']['config_other'] = json_decode($post['CrmSystemYgxx_']['config_other']);
        if(!empty($ygxx) && $ygxx->load($post) && $ygxx->update()){
            return $this->dealFD('保存成功',1);
        }else{
            return $this->dealFD('保存失败',0);
        }
    }

    /**
     * 今天新增意向的数据列表
     * @return string
     */
    public function actionTodayAddIntentAccountList(){
        $search = new CrmHwzxSearch();
        $pro = $search->search(\Yii::$app->request->post());
        $pro->query->andWhere(['>','gxsj',$this->todaySec])->andWhere(['is not','khxq',null])->andWhere(['!=','khxq','']);
        $count = $pro->query->count();
        isset($_POST['limit'])?$pro->query->limit($_POST['limit']):"";
        isset($_POST['page'])?$pro->query->offset($_POST['limit']*($_POST['page']-1)):"";
        $res = $pro->query->asArray()->all();
        return $this->actionLayData($count,$res,1,"");
    }

    /**
     * 部门排行列表
     * @return string
     */
    public function actionBmRangeList(){
        $orderBy = isset($_POST['orderBy'])?' order by '.$_POST['orderBy'] . ' desc ':" ORDER BY yxkhs desc ";
        $rangeClassify = isset($_POST['rangeClassify'])?$_POST['rangeClassify'] :'day';
        if ($rangeClassify=='day'){
            $rangeClassify = $this->todaySec;
        }
        if ($rangeClassify=='week'){
            $rangeClassify = $this->weekSec;
        }
        if ($rangeClassify=='month'){
            $rangeClassify = $this->monthSec;
        }
        $sql = "select *,@i:=@i+1 as ph from
(
	select e.*,sum(zje) as qyje from
	(
		select * from 
		(
			select 
			c.name as bm,c.user_id,c.ygmc as ygxm,c.zxhm
			,count(if((c.khxq is not null)&&(c.khxq!=''),true,null)) as yxkhs
			,count(if(c.wcj=3,true,null)) as qykhs
			from
				(select b.*,h.wcj,h.khxq from 
					(
						select a.*,y.user_id,y.zxhm,y.ygmc from 
							(select * from crm_system_gsxx where pid=1) as a
						left join crm_system_ygxx as y
						on y.dlxl like concat('%',a.id,'%')
					) as b
					left join crm_hwzx as h
					on b.user_id=h.gsid and h.gxsj>".$rangeClassify."
				)as c
			GROUP BY bm
		)as d
		left join crm_mall_order as o
		on o.gsid=d.user_id and o.xdsj>".$rangeClassify."
	) as e
	GROUP BY bm
" . $orderBy . ") as f, (select @i :=".$_POST['limit']*($_POST['page']-1).") as i";//查询部门排行的sql语句
        $count = \Yii::$app->db->createCommand($sql)->query()->count();
        $sql .= isset($_POST['limit'])?' limit '.$_POST['limit']:"";
        $sql .= isset($_POST['page'])?' offset '.$_POST['limit']*($_POST['page']-1):"";
        $res = \Yii::$app->db->createCommand($sql)->queryAll();
        return $this->actionLayData($count,$res);
    }
    /**
     * 个人排行列表
     * @return string
     */
    public function actionGrRangeList(){
        $orderBy = isset($_POST['orderBy'])?' order by '.$_POST['orderBy'] . ' desc ':" ORDER BY yxkhs desc ";
        $rangeClassify = isset($_POST['rangeClassify'])?$_POST['rangeClassify'] :'day';
        if ($rangeClassify=='day'){
            $rangeClassify = $this->todaySec;
        }
        if ($rangeClassify=='week'){
            $rangeClassify = $this->weekSec;
        }
        if ($rangeClassify=='month'){
            $rangeClassify = $this->monthSec;
        }
        $sql = "select 
	*,@i:=@i+1 as ph from
	(select 
		*,sum(duration) as thsc from
		(select 
			*,sum(zje) as qyje
			from
			(
				select b.*,o.zje,o.xdsj from
				(
					select 
					a.name as bm,a.gsid,a.ygmc as ygxm,a.zxhm
					,count(if((a.khxq is not null)&&(a.khxq!=''),true,null)) as yxkhs
					,count(if(a.wcj=3,true,null)) as qykhs
					from
						(
						select g.*,h.khxq,h.gsid,y.ygmc,y.zxhm,h.wcj from crm_system_ygxx as y left join  crm_system_gsxx as g on y.dlxl like concat('%',g.id,'%') and g.pid=1 left join crm_hwzx as h on y.user_id=h.gsid and h.gxsj>".$rangeClassify."
						) as a
					GROUP BY ygxm
				) as b
				left join crm_mall_order as o 
				on o.gsid=b.gsid and o.xdsj>".$rangeClassify."
			) as c
			GROUP BY ygxm) as d
	left JOIN cti_cdr as cc
	on cc.user_name = d.zxhm and cc.start_epoch>".$rangeClassify."
	GROUP BY ygxm
" . $orderBy .") as e, (select @i :=".$_POST['limit']*($_POST['page']-1).") as i";//查询部门排行的sql语句
        $count = \Yii::$app->db->createCommand($sql)->query()->count();
        $sql .= isset($_POST['limit'])?' limit '.$_POST['limit']:"";
        $sql .= isset($_POST['page'])?' offset '.$_POST['limit']*($_POST['page']-1):"";
        $res = \Yii::$app->db->createCommand($sql)->queryAll();
        return $this->actionLayData($count,$res);
    }



}